USE [SIMS]
GO
/****** Object:  StoredProcedure [dbo].[sprocEducationPeriodTableSelectList]    Script Date: 07/06/2012 15:11:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sprocEducationPeriodTableSelectList]
@SchoolKey int,
@LocationKey int,
@StandardLevelKey int,
@StartYear varchar(4) = NULL
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		ep.[EducationPeriodKey],
		ep.[SchoolKey],
		ep.[LocationKey],
		l.[LocationCode],
		ep.[StandardLevelKey],
		stl.[StandardLevelCode],
		ep.[EducationPeriodCode],
		ep.[Description],
		ep.[StartDate],
		ep.[EndDate],
		ep.[Status],
		ep.[CreatedDateTime],
		ep.[ModifiedDateTime]
	FROM [EducationPeriodTable] ep 
	
	INNER JOIN StandardLevelTable stl
	ON ep.StandardLevelKey = stl.StandardLevelKey AND ep.SchoolKey = stl.SchoolKey
	
	INNER JOIN LocationTable l
	ON ep.LocationKey = l.LocationKey AND ep.SchoolKey = l.SchoolKey
	
	WHERE ep.SchoolKey = @SchoolKey AND ep.LocationKey = @LocationKey And ep.StandardLevelKey = @StandardLevelKey
	
	AND  (@StartYear IS NULL OR (@StartYear IS NOT NULL AND  CONVERT(varchar(4), ep.StartDate, 112) = @StartYear))
	
	SET @Err = @@Error

	RETURN @Err
END
